package o2o.web.actions.statis;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.jinyou.utils.common.*;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import o2o.cache.shop.ShopMemory;
import o2o.cache.user.UserInfoMemory;
import o2o.cache.user.UserOnlineImpl;
import o2o.config.dao.UserDaoSql;
import o2o.dao.PO.PojoTables;
import o2o.dao.PO.common.UserType;
import o2o.dao.PO.order.ORDER_STATUS;
import o2o.company.model.ShopInfo;
import o2o.dao.PO.user.UserInfo;
import o2o.dao.VO.order.REFUND_APPLY_STATUS;
import o2o.dao.VO.statis.OrderFPVO;
import o2o.system.data.SYS_CONFIG_PATH;
import o2o.system.data.SYS_SETTINGS;
import o2o.task.order.OrderBakTaskUtils;
import o2o.web.actions.company.SysCityCompanyMan;
import o2o.web.actions.user.User;
import org.apache.commons.io.FileUtils;
import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;

import java.io.File;
import java.sql.SQLException;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @Description: 订单统计
 * Created with IntelliJ IDEA.
 * User: Jahnke [1029777564@qq.com]
 * Date: 2019-02-26 9:48
 * To change this template use File | Settings | File Templates.
 */
@Controller
public class OrderStatisController {

    // 按时段统计订单信息(数量)（24h内的，返回每小时的数据； 超过24H，返回每天的数据）
    @RequestMapping("admin/count/order/statistics")
    @ResponseBody
    public String orderStatisByTime(String token, Long shopId, Long startTime, Long endTime, String province, String city, String county) {

        // 验证用户有效性
        UserInfo user = UserInfoMemory.getInstance().getUserInfo(UserOnlineImpl.getInstance().get(token));
        if (user == null) {
            return AjaxResult.errorLogoutResult();
        }

        if (user.getUserType() != UserType.ADMIN && user.getUserType() != UserType.SUPER_ADMIN && user.getUserType() != UserType.SHOP_OWNER) {
            return AjaxResult.successResult(Collections.EMPTY_LIST, 0);
        }
        if (user.getUserType() == UserType.SHOP_OWNER) {
            ShopInfo shopInfo = ShopMemory.getInstance().getShopByUsername(user.getUsername());
            shopId = shopInfo.getId();
        }
        if (startTime == null) {
            startTime = DateUtils.getFirstDayOfMonth(new Date()).getTime();
        }
        if (endTime == null) {
            endTime = new Date().getTime();
        }
        /*
            SELECT COUNT(ori.orderno ),
                SUM(IFNULL(orp.totalPrice,0)),
                FROM_UNIXTIME(ori.createTime/1000,'%Y-%m-%d %h') AS odate
            FROM order_info AS ori
            LEFT JOIN order_pay AS orp ON orp.orderno=ori.orderno
            LEFT JOIN city_agent AS cag ON cag.id=ori.agentId
            LEFT JOIN user_agent_relation AS uar ON uar.agentId=cag.id
            GROUP BY odate
         */
        if (null != SYS_SETTINGS.HAS_MULTILINGUAL && 0 == 1 - SYS_SETTINGS.HAS_MULTILINGUAL) {//多语言版
            if (ValidateUtil.isNotNull(city)) {
                city = JYPinyinHelper.isChinese(city) ? JYPinyinHelper.toHanyuPinyin(city) : city.toLowerCase();
            }
        }

        String t_order_info = PojoTables.OrderInfo;
        String t_order_pay = PojoTables.OrderPay;
        if (null != SYS_SETTINGS.USE_BAK_ORDER && 0 == 1 - SYS_SETTINGS.USE_BAK_ORDER) {
            String dataDbName = OrderBakTaskUtils.getDataDbName();
            if (ValidateUtil.isNotNull(dataDbName)) {
                t_order_info = dataDbName + "." + PojoTables.D_ORDER_INFO;
                t_order_pay = dataDbName + "." + PojoTables.D_ORDER_PAY;
            }
        }

        String dateFormat = endTime - startTime < DateUtils.oneDayMills ? "%Y-%m-%d %h" : "%Y-%m-%d";
//        String dateFormat = "%h";
        String sql = "SELECT COUNT(ori.orderno) orderCount, " +
                "        SUM(IFNULL(ori.totalPrice,0)) totalPrice, " +
                "        FROM_UNIXTIME(ori.createTime/1000,'" + dateFormat + "') AS odate " +
//                "        FROM_UNIXTIME(ori.createTime/1000,'" + dateFormat + "') odate," +
                " FROM " + t_order_info + " AS ori " +
                " LEFT JOIN " + t_order_pay + " AS orp ON orp.orderno=ori.orderno " +
                " LEFT JOIN " + PojoTables.CITY_AGENT + " AS cag ON cag.id=ori.agentId " +
                " LEFT JOIN " + PojoTables.USER_AGENT_RELATION + " AS uar ON uar.agentId=cag.id " +
                " LEFT JOIN " + PojoTables.ShopInfo + " AS sfo on sfo.id=ori.shopId " +
                " WHERE ori.orderStatus=" + ORDER_STATUS.OVER + " AND ori.delFlag=0 " +
                (shopId != null && shopId > 0 ? " and ori.shopId=" + shopId : "") +
                (StringUtils.isNotEmpty(province) ? (" AND sfo.province='" + province + "'") : "") +
                (StringUtils.isNotEmpty(city) ? (" AND sfo.city='" + city + "'") : "") +
                (StringUtils.isNotEmpty(county) ? (" AND sfo.county='" + county + "'") : "") +
                " AND ori.createtime BETWEEN " + startTime + " AND " + endTime +
                (user.getUserType() == UserType.ADMIN ? (" AND uar.agentUsername='" + user.getUsername() + "'") : "") +
                " GROUP BY odate";
        List<HashMap<String, Object>> list = null;
        try {
            list = UserDaoSql.getInstance().queryBySql(sql);
        } catch (SQLException e) {
            return AjaxResult.errorResult(e.getMessage());
        }
        Map<String, HashMap<String, Object>> re = new LinkedHashMap<>();
        for (long i = startTime; i < endTime; ) {
            String d = endTime - startTime < DateUtils.oneDayMills ? DateUtils.sdfDateTime.get().format(i).substring(0, 13) : DateUtils.sdfDate.get().format(i);
//            String d = DateUtils.sdfDateTime.get().format(i).substring(11, 13);
//            i = i + DateUtils.oneHourMills;
            if (endTime - startTime < DateUtils.oneDayMills) {
                i = i + DateUtils.oneHourMills;
            } else {
                i = i + DateUtils.oneDayMills;
            }
            HashMap<String, Object> hh = new HashMap<>();
            hh.put("odate", d);
            hh.put("orderCount", 0);
            hh.put("totalPrice", 0D);
            re.put(d, hh);
        }
        Map<String, HashMap<String, Object>> dd = null;
        // 获取准确的时间
        if (list != null && !list.isEmpty()) {
            dd = new HashMap<>();
            for (int i = 0; i < list.size(); i++) {
                String d = list.get(i).get("odate").toString();
                dd.put(d, list.get(i));
            }
        }
        for (Map.Entry<String, HashMap<String, Object>> dv : re.entrySet()) {
            String odate = dv.getKey();
            if (dd != null && dd.containsKey(odate)) {
                re.put(odate, dd.get(odate));       // 重置当前时间数据
            }
        }
        // 将map转list
        List<HashMap<String, Object>> lll = re.values().stream().collect(Collectors.toList());

        return AjaxResult.successResult(lll == null ? 0 : lll.size(), lll);
    }


    // 按时段统计订单信息(数量)（24h内的，返回每小时的数据； 超过24H，返回每天的数据）
    @RequestMapping("admin/count/order/statistics_v3")
    @ResponseBody
    public String orderStatisByTimes(String token, Long shopId, Long startTime, Long endTime, String province, String city, String county) {

        // 验证用户有效性
        UserInfo user = UserInfoMemory.getInstance().getUserInfo(UserOnlineImpl.getInstance().get(token));
        if (user == null) {
            return AjaxResult.errorLogoutResult();
        }

        if (user.getUserType() != UserType.ADMIN && user.getUserType() != UserType.SUPER_ADMIN && user.getUserType() != UserType.SHOP_OWNER) {
            return AjaxResult.successResult(Collections.EMPTY_LIST, 0);
        }
        if (user.getUserType() == UserType.SHOP_OWNER) {
            ShopInfo shopInfo = ShopMemory.getInstance().getShopByUsername(user.getUsername());
            shopId = shopInfo.getId();
        }
        if (startTime == null) {
            startTime = DateUtils.getFirstDayOfMonth(new Date()).getTime();
        }
        if (endTime == null) {
            endTime = new Date().getTime();
        }
        /*
            SELECT COUNT(ori.orderno ),
                SUM(IFNULL(orp.totalPrice,0)),
                FROM_UNIXTIME(ori.createTime/1000,'%Y-%m-%d %h') AS odate
            FROM order_info AS ori
            LEFT JOIN order_pay AS orp ON orp.orderno=ori.orderno
            LEFT JOIN city_agent AS cag ON cag.id=ori.agentId
            LEFT JOIN user_agent_relation AS uar ON uar.agentId=cag.id
            GROUP BY odate
         */
//        String dateFormat = endTime - startTime < DateUtils.oneDayMills ? "%Y-%m-%d %h" : "%Y-%m-%d";

        String t_order_info = PojoTables.OrderInfo;
        String t_order_pay = PojoTables.OrderPay;
        if (null != SYS_SETTINGS.USE_BAK_ORDER && 0 == 1 - SYS_SETTINGS.USE_BAK_ORDER) {
            String dataDbName = OrderBakTaskUtils.getDataDbName();
            if (ValidateUtil.isNotNull(dataDbName)) {
                t_order_info = dataDbName + "." + PojoTables.D_ORDER_INFO;
                t_order_pay = dataDbName + "." + PojoTables.D_ORDER_PAY;
            }
        }

        if (null != SYS_SETTINGS.HAS_MULTILINGUAL && 0 == 1 - SYS_SETTINGS.HAS_MULTILINGUAL) {//多语言版
            if (ValidateUtil.isNotNull(city)) {
                city = JYPinyinHelper.isChinese(city) ? JYPinyinHelper.toHanyuPinyin(city) : city.toLowerCase();
            }
        }

        String dateFormat = "%h";
        String sql = "SELECT COUNT(ori.orderno) orderCount, " +
                "        SUM(IFNULL(ori.totalPrice,0)) totalPrice, " +
                "        FROM_UNIXTIME(ori.createTime/1000,'" + dateFormat + "') AS odate " +
//                "        FROM_UNIXTIME(ori.createTime/1000,'" + dateFormat + "') odate," +
                " FROM " + t_order_info + " AS ori " +
                " LEFT JOIN " + t_order_pay + " AS orp ON orp.orderno=ori.orderno " +
                " LEFT JOIN " + PojoTables.CITY_AGENT + " AS cag ON cag.id=ori.agentId " +
                " LEFT JOIN " + PojoTables.USER_AGENT_RELATION + " AS uar ON uar.agentId=cag.id " +
                " LEFT JOIN " + PojoTables.ShopInfo + " AS sfo on sfo.id=ori.shopId " +
                " WHERE ori.orderStatus=" + ORDER_STATUS.OVER + " AND ori.delFlag=0 " +
                (shopId != null && shopId > 0 ? " and ori.shopId=" + shopId : "") +
                (StringUtils.isNotEmpty(province) ? (" AND sfo.province='" + province + "'") : "") +
                (StringUtils.isNotEmpty(city) ? (" AND sfo.city='" + city + "'") : "") +
                (StringUtils.isNotEmpty(county) ? (" AND sfo.county='" + county + "'") : "") +
                " AND ori.createtime BETWEEN " + startTime + " AND " + endTime +
                (user.getUserType() == UserType.ADMIN ? (" AND uar.agentUsername='" + user.getUsername() + "'") : "") +
                " GROUP BY odate";
        List<HashMap<String, Object>> list = null;
        try {
            list = UserDaoSql.getInstance().queryBySql(sql);
        } catch (SQLException e) {
            return AjaxResult.errorResult(e.getMessage());
        }
        Map<String, HashMap<String, Object>> re = new LinkedHashMap<>();
        for (long i = startTime; i < endTime; ) {
//            String d = endTime - startTime < DateUtils.oneDayMills ? DateUtils.sdfDateTime.get().format(i).substring(0, 13) : DateUtils.sdfDate.get().format(i);
            String d = DateUtils.sdfDateTime.get().format(i).substring(11, 13);
            i = i + DateUtils.oneHourMills;
//            if (endTime - startTime < DateUtils.oneDayMills) {
//                i = i + DateUtils.oneHourMills;
//            } else {
//                i = i + DateUtils.oneDayMills;
//            }
            HashMap<String, Object> hh = new HashMap<>();
            hh.put("odate", d);
            hh.put("orderCount", 0);
            hh.put("totalPrice", 0D);
            re.put(d, hh);
        }
        Map<String, HashMap<String, Object>> dd = null;
        // 获取准确的时间
        if (list != null && !list.isEmpty()) {
            dd = new HashMap<>();
            for (int i = 0; i < list.size(); i++) {
                String d = list.get(i).get("odate").toString();
                dd.put(d, list.get(i));
            }
        }
        for (Map.Entry<String, HashMap<String, Object>> dv : re.entrySet()) {
            String odate = dv.getKey();
            if (dd != null && dd.containsKey(odate)) {
                re.put(odate, dd.get(odate));       // 重置当前时间数据
            }
        }
        // 将map转list
        List<HashMap<String, Object>> lll = re.values().stream().collect(Collectors.toList());

        return AjaxResult.successResult(lll == null ? 0 : lll.size(), lll);
    }

    // 按时段统计订单信息(数量)（返回每小时的数据）
    @RequestMapping("admin/count/order/statistics-v2")
    @ResponseBody
    public String orderStatisByTimeV2(String token, Long shopId, Long startTime, Long endTime, String province, String city, String county) {

        // 验证用户有效性
        UserInfo user = UserInfoMemory.getInstance().getUserInfo(UserOnlineImpl.getInstance().get(token));
        if (user == null) {
            return AjaxResult.errorLogoutResult();
        }

//        if (user.getUserType() != UserType.ADMIN && user.getUserType() != UserType.SUPER_ADMIN && user.getUserType() != UserType.SHOP_OWNER) {
        if (user.getUserType() != UserType.ADMIN && user.getUserType() != UserType.SUPER_COMPANY && user.getUserType() != UserType.COMPANY && user.getUserType() != UserType.SUPER_ADMIN && user.getUserType() != UserType.SHOP_OWNER) {
            return AjaxResult.successResult(Collections.EMPTY_LIST, 0);
        }
        if (user.getUserType() == UserType.SHOP_OWNER) {
            ShopInfo shopInfo = ShopMemory.getInstance().getShopByUsername(user.getUsername());
            shopId = shopInfo.getId();
        }
        if (startTime == null) {
            startTime = DateUtils.getFirstDayOfMonth(new Date()).getTime();
        }
        if (endTime == null) {
            endTime = new Date().getTime();
        }
        if (user.getUserType() == UserType.COMPANY) {
            user.setUserType(UserType.ADMIN);
            String username = SysCityCompanyMan.getSysCompanyOwnUsername(user.getUsername());
            if (StringUtils.isEmpty(username)) {
                return AjaxResult.successResult(Collections.EMPTY_LIST, 0);
            }
            user.setUsername(username);
        }

        String t_order_info = PojoTables.OrderInfo;
        String t_order_pay = PojoTables.OrderPay;
        if (null != SYS_SETTINGS.USE_BAK_ORDER && 0 == 1 - SYS_SETTINGS.USE_BAK_ORDER) {
            String dataDbName = OrderBakTaskUtils.getDataDbName();
            if (ValidateUtil.isNotNull(dataDbName)) {
                t_order_info = dataDbName + "." + PojoTables.D_ORDER_INFO;
                t_order_pay = dataDbName + "." + PojoTables.D_ORDER_PAY;
            }
        }

        if (null != SYS_SETTINGS.HAS_MULTILINGUAL && 0 == 1 - SYS_SETTINGS.HAS_MULTILINGUAL) {//多语言版
            if (ValidateUtil.isNotNull(city)) {
                city = JYPinyinHelper.isChinese(city) ? JYPinyinHelper.toHanyuPinyin(city) : city.toLowerCase();
            }
        }

        /*
            SELECT COUNT(ori.orderno ),
                SUM(IFNULL(orp.totalPrice,0)),
                FROM_UNIXTIME(ori.createTime/1000,'%Y-%m-%d %h') AS odate
            FROM order_info AS ori
            LEFT JOIN order_pay AS orp ON orp.orderno=ori.orderno
            LEFT JOIN city_agent AS cag ON cag.id=ori.agentId
            LEFT JOIN user_agent_relation AS uar ON uar.agentId=cag.id
            GROUP BY odate
         */
        String dateFormat = "%H";
        String payType = "";
        String sql = "SELECT COUNT(ori.orderno) orderCount, FROM_UNIXTIME(ori.createTime/1000,'" + dateFormat + "') odate," +
                "           SUM(CASE WHEN ori.payType='alipay' or (ori.payType='yiyi' and ori.payType2='ali') THEN 1 ELSE 0 END) aliCount, " +
                "           SUM(CASE WHEN ori.payType='wx' or (ori.payType='yiyi' and ori.payType2='wx') THEN 1 ELSE 0 END) wxCount, " +
                "           SUM(CASE WHEN ori.payType='wx_xcx' or (ori.payType='yiyi' and ori.payType2='wx_xcx') THEN 1 ELSE 0 END) wxxCount, " +
                "           SUM(CASE WHEN ori.payType='transit' or (ori.payType='yiyi' and ori.payType2='transit') THEN 1 ELSE 0 END) transitCount, " +
                "           SUM(CASE WHEN ori.payType='wallet' or (ori.payType='yiyi' and ori.payType2='wallet') THEN 1 ELSE 0 END) walletCount, " +
                "           SUM(CASE WHEN ori.payType='paypal' or (ori.payType='yiyi' and ori.payType2='paypal') THEN 1 ELSE 0 END) paypalCount, " +
                "           SUM(CASE WHEN ori.payType='arrive' or (ori.payType='yiyi' and ori.payType2='arrive') THEN 1 ELSE 0 END) arriveCount, " +
                "           SUM(CASE WHEN ori.payType='stripe' or (ori.payType='yiyi' and ori.payType2='stripe') THEN 1 ELSE 0 END) stripeCount, " +
                "           SUM(CASE WHEN (ori.payType!='wallet' AND ori.payType!='paypal' AND ori.payType!='arrive' AND ori.payType!='transit' AND ori.payType!='alipay' AND ori.payType!='wx' AND ori.payType!='wx_xcx' AND ori.payType!='stripe' AND ori.payType is not null and ori.payType!='yiyi') " +
                " or (ori.payType2!='wallet' and ori.payType2!='paypal' and ori.payType2!='arrive' and ori.payType2!='transit' and ori.payType2!='ali' AND ori.payType2!='wx' AND ori.payType2!='wx_xcx' AND ori.payType2!='stripe' AND ori.payType2 is not null and ori.payType ='yiyi') THEN 1 ELSE 0 END) othCount, " +
                "           SUM(ori.totalPrice) totalPrice, " +
                "           SUM(CASE WHEN ori.payType='alipay' or (ori.payType='yiyi' and ori.payType2='ali') THEN ori.totalPrice ELSE 0 END) aliPrice, " +
                "           SUM(CASE WHEN ori.payType='wx' or (ori.payType='yiyi' and ori.payType2='wx') THEN ori.totalPrice ELSE 0 END) wxPrice, " +
                "           SUM(CASE WHEN ori.payType='wx_xcx' or (ori.payType='yiyi' and ori.payType2='wx_xcx') THEN ori.totalPrice ELSE 0 END) wxxPrice, " +
                "           SUM(CASE WHEN ori.payType='transit' or (ori.payType='yiyi' and ori.payType2='transit') THEN ori.totalPrice ELSE 0 END) transitPrice, " +
                "           SUM(CASE WHEN ori.payType='wallet' or (ori.payType='yiyi' and ori.payType2='wallet') THEN ori.totalPrice ELSE 0 END) walletPrice, " +
                "           SUM(CASE WHEN ori.payType='paypal' or (ori.payType='yiyi' and ori.payType2='paypal') THEN ori.totalPrice ELSE 0 END) paypalPrice, " +
                "           SUM(CASE WHEN ori.payType='arrive' or (ori.payType='yiyi' and ori.payType2='arrive') THEN ori.totalPrice ELSE 0 END) arrivePrice, " +
                "           SUM(CASE WHEN ori.payType='stripe' or (ori.payType='yiyi' and ori.payType2='stripe') THEN ori.totalPrice ELSE 0 END) stripePrice, " +
                "           SUM(CASE WHEN (ori.payType!='wallet' AND ori.payType!='paypal' AND ori.payType!='arrive' AND ori.payType!='alipay' AND ori.payType!='transit' AND ori.payType!='wx' AND ori.payType!='wx_xcx' AND ori.payType!='stripe' AND ori.payType is not null and ori.payType!='yiyi') " +
                " or (ori.payType2!='wallet' AND ori.payType2!='paypal' AND ori.payType2!='arrive' AND ori.payType2!='ali' AND ori.payType2!='transit' AND ori.payType2!='wx' AND ori.payType2!='wx_xcx' AND ori.payType2!='stripe' AND ori.payType2 is not null and ori.payType='yiyi') THEN ori.totalPrice ELSE 0 END) othPrice " +
                " FROM " + t_order_info + " AS ori " +
                " LEFT JOIN " + t_order_pay + " AS orp ON orp.orderno=ori.orderno " +
                " LEFT JOIN " + PojoTables.CITY_AGENT + " AS cag ON cag.id=ori.agentId " +
                " LEFT JOIN " + PojoTables.USER_AGENT_RELATION + " AS uar ON uar.agentId=cag.id " +
                " LEFT JOIN " + PojoTables.ShopInfo + " AS sfo on sfo.id=ori.shopId " +
                " WHERE ori.orderStatus=" + ORDER_STATUS.OVER + " AND ori.delFlag=0 " +
                (shopId != null && shopId > 0 ? " and ori.shopId=" + shopId : "") +
                (StringUtils.isNotEmpty(province) ? (" AND sfo.province='" + province + "'") : "") +
                (StringUtils.isNotEmpty(city) ? (" AND sfo.city='" + city + "'") : "") +
                (StringUtils.isNotEmpty(county) ? (" AND sfo.county='" + county + "'") : "") +
                " AND ori.createtime BETWEEN " + startTime + " AND " + endTime +
                (user.getUserType() == UserType.ADMIN ? (" AND uar.agentUsername='" + user.getUsername() + "'") : "") +
                " GROUP BY odate";
        String sqlTotal = "SELECT " +
                "           COUNT(ori.orderno) orderCount, " +
                "           SUM(CASE WHEN ori.payType='alipay' or (ori.payType='yiyi' and ori.payType2='ali') THEN 1 ELSE 0 END) aliCount, " +
                "           SUM(CASE WHEN ori.payType='wx' or (ori.payType='yiyi' and ori.payType2='wx') THEN 1 ELSE 0 END) wxCount, " +
                "           SUM(CASE WHEN ori.payType='transit' or (ori.payType='yiyi' and ori.payType2='transit') THEN 1 ELSE 0 END) transitCount, " +
                "           SUM(CASE WHEN ori.payType='wx_xcx' or (ori.payType='yiyi' and ori.payType2='wx_xcx') THEN 1 ELSE 0 END) wxxCount, " +
                "           SUM(CASE WHEN ori.payType='wallet' or (ori.payType='yiyi' and ori.payType2='wallet') THEN 1 ELSE 0 END) walletCount, " +
                "           SUM(CASE WHEN ori.payType='paypal' or (ori.payType='yiyi' and ori.payType2='paypal') THEN 1 ELSE 0 END) paypalCount, " +
                "           SUM(CASE WHEN ori.payType='arrive' or (ori.payType='yiyi' and ori.payType2='arrive') THEN 1 ELSE 0 END) arriveCount, " +
                "           SUM(CASE WHEN ori.payType='stripe' or (ori.payType='yiyi' and ori.payType2='stripe') THEN 1 ELSE 0 END) stripeCount, " +
                "           SUM(CASE WHEN (ori.payType!='wallet' AND ori.payType!='paypal' AND ori.payType!='arrive' AND ori.payType!='transit' AND ori.payType!='alipay' AND ori.payType!='wx' AND ori.payType!='wx_xcx' AND ori.payType!='stripe'  AND ori.payType is not null and ori.payType!='yiyi') " +
                " or (ori.payType2!='wallet' AND ori.payType2!='paypal' AND ori.payType2!='arrive' AND ori.payType2!='transit' AND ori.payType2!='ali' AND ori.payType2!='wx' AND ori.payType2!='wx_xcx' AND ori.payType2!='stripe' AND ori.payType2 is not null and ori.payType='yiyi') THEN 1 ELSE 0 END) othCount, " +
                "           SUM(ori.totalPrice) totalPrice, " +
                "           SUM(CASE WHEN ori.payType='alipay' or (ori.payType='yiyi' and ori.payType2='ali') THEN ori.totalPrice ELSE 0 END) aliPrice, " +
                "           SUM(CASE WHEN ori.payType='wx' or (ori.payType='yiyi' and ori.payType2='wx') THEN ori.totalPrice ELSE 0 END) wxPrice, " +
                "           SUM(CASE WHEN ori.payType='transit' or (ori.payType='yiyi' and ori.payType2='transit') THEN ori.totalPrice ELSE 0 END) transitPrice, " +
                "           SUM(CASE WHEN ori.payType='wallet' or (ori.payType='yiyi' and ori.payType2='wallet') THEN ori.totalPrice ELSE 0 END) walletPrice, " +
                "           SUM(CASE WHEN ori.payType='paypal' or (ori.payType='yiyi' and ori.payType2='paypal') THEN ori.totalPrice ELSE 0 END) paypalPrice, " +
                "           SUM(CASE WHEN ori.payType='arrive' or (ori.payType='yiyi' and ori.payType2='arrive') THEN ori.totalPrice ELSE 0 END) arrivePrice, " +
                "           SUM(CASE WHEN ori.payType='stripe' or (ori.payType='yiyi' and ori.payType2='stripe') THEN ori.totalPrice ELSE 0 END) stripePrice, " +
                "           SUM(CASE WHEN ori.payType='wx_xcx' or (ori.payType='yiyi' and ori.payType2='wx_xcx') THEN ori.totalPrice ELSE 0 END) wxxPrice, " +
                "           SUM(CASE WHEN (ori.payType!='wallet' AND ori.payType!='paypal' AND ori.payType!='arrive' AND ori.payType!='transit' AND ori.payType!='alipay' AND ori.payType!='wx' AND ori.payType!='wx_xcx'  AND ori.payType!='stripe' AND ori.payType is not null and ori.payType!='yiyi') " +
                " or (ori.payType2!='wallet' AND ori.payType2!='paypal' AND ori.payType2!='arrive' AND ori.payType2!='transit' AND ori.payType2!='ali' AND ori.payType2!='wx' AND ori.payType2!='wx_xcx' AND ori.payType2!='stripe' AND ori.payType2 is not null and ori.payType='yiyi') THEN ori.totalPrice ELSE 0 END) othPrice " +
                " FROM " + t_order_info + " AS ori " +
                " LEFT JOIN user_agent_relation AS uar ON uar.agentid=ori.agentId  " +
                " LEFT JOIN shop_info as sfo on sfo.id=ori.shopId " +
                " WHERE ori.delFlag=0 AND ori.orderStatus=9  " +
                " AND ori.createTime BETWEEN " + startTime + " AND " + endTime +
                (shopId != null ? " AND ori.shopId=" + shopId : "") +
                (StringUtils.isNotEmpty(province) ? (" AND sfo.province='" + province + "'") : "") +
                (StringUtils.isNotEmpty(city) ? (" AND sfo.city='" + city + "'") : "") +
                (StringUtils.isNotEmpty(county) ? (" AND sfo.county='" + county + "'") : "") +
                (user.getUserType() == UserType.ADMIN ? (" AND uar.username='" + user.getUsername() + "'") : "");
        String sqlCount = "SELECT COUNT(*) COUNTs FROM(SELECT FROM_UNIXTIME(ori.createTime/1000,'%Y-%m-%d') ordate, " +
                "           COUNT(ori.orderno) orderCount " +
                " FROM " + t_order_info + " AS ori  " +
                " LEFT JOIN " + PojoTables.USER_AGENT_RELATION + " AS uar ON uar.agentid=ori.agentId  " +
                " LEFT JOIN " + PojoTables.ShopInfo + " as sfo on sfo.id=ori.shopId " +
                " WHERE ori.delFlag=0 AND ori.orderStatus=" + ORDER_STATUS.OVER + "  " +
                " AND ori.createTime BETWEEN " + startTime + " AND " + endTime +
                (shopId != null ? " AND ori.shopId=" + shopId : "") +
                (StringUtils.isNotEmpty(province) ? (" AND sfo.province='" + province + "'") : "") +
                (StringUtils.isNotEmpty(city) ? (" AND sfo.city='" + city + "'") : "") +
                (StringUtils.isNotEmpty(county) ? (" AND sfo.county='" + county + "'") : "") +
                (user.getUserType() == UserType.ADMIN ? (" AND uar.username='" + user.getUsername() + "'") : "") +
                " GROUP BY ordate )a";
        List<HashMap<String, Object>> list = null;
        HashMap<String, Object> info = null;
        long totalCount = 0;
        try {
            list = UserDaoSql.getInstance().queryBySql(sql);
            info = UserDaoSql.getInstance().queryMapBySql(sqlTotal);
            totalCount = UserDaoSql.getInstance().getCount(sqlCount);
        } catch (SQLException e) {
            return AjaxResult.errorResult(e.getMessage());
        }
        Map<String, HashMap<String, Object>> re = new LinkedHashMap<>();
        for (long i = startTime; i < endTime; ) {
            String d = DateUtils.sdfDateTime.get().format(i).substring(11, 13);
            i = i + DateUtils.oneHourMills;

            HashMap<String, Object> hh = new HashMap<>();
            hh.put("odate", d);
            hh.put("orderCount", 0);
            hh.put("aliCount", 0);
            hh.put("wxCount", 0);
            hh.put("transitCount", 0);
            hh.put("wxxCount", 0);
            hh.put("walletCount", 0);
            hh.put("paypalCount", 0);
            hh.put("arriveCount", 0);
            hh.put("stripeCount", 0);
            hh.put("othCount", 0);
            hh.put("totalPrice", 0);
            hh.put("aliPrice", 0);
            hh.put("wxPrice", 0);
            hh.put("wxxPrice", 0);
            hh.put("walletPrice", 0);
            hh.put("paypalPrice", 0);
            hh.put("arrivePrice", 0);
            hh.put("transitPrice", 0);
            hh.put("stripePrice", 0);
            hh.put("othPrice", 0);
            re.put(d, hh);
        }
        Map<String, HashMap<String, Object>> dd = null;
        // 获取准确的时间
        if (list != null && !list.isEmpty()) {
            dd = new HashMap<>();
            for (int i = 0; i < list.size(); i++) {
                String d = list.get(i).get("odate").toString();
                dd.put(d, list.get(i));
            }
        }
        for (Map.Entry<String, HashMap<String, Object>> dv : re.entrySet()) {
            String odate = dv.getKey();
            if (dd != null && dd.containsKey(odate)) {
                re.put(odate, dd.get(odate));       // 重置当前时间数据
            }
        }
        // 将map转list
        List<HashMap<String, Object>> lll = re.values().stream().collect(Collectors.toList());
        JSONObject obj = new JSONObject();
        obj.put("status", 1);
        obj.put("totalCount", totalCount);
        obj.put("data", JSONArray.toJSON(lll));
        obj.put("size", lll.size());
        obj.put("info", JSONObject.toJSON(info));
        return obj.toString();
//        return AjaxResult.successResult(lll == null ? 0 : lll.size(), lll);
    }

    // 平台最近三天统计数据对比分析
    @RequestMapping("admin/plate/order-user/statistics")
    @ResponseBody
    public String comparedStatis(String token, Long shopId, String province, String city, String county) {
        // 验证用户有效性
        UserInfo user = UserInfoMemory.getInstance().getUserInfo(UserOnlineImpl.getInstance().get(token));
        if (user == null) {
            return AjaxResult.errorLogoutResult();
        }

        if (user.getUserType() != UserType.ADMIN && user.getUserType() != UserType.SUPER_ADMIN && user.getUserType() != UserType.SHOP_OWNER) {
            return AjaxResult.successResult(Collections.EMPTY_LIST, 0);
        }
        if (user.getUserType() == UserType.SHOP_OWNER) {
            ShopInfo shopInfo = ShopMemory.getInstance().getShopByUsername(user.getUsername());
            shopId = shopInfo.getId();
        }
        Long todayDay = new Date().getTime();
        Long secondDay = DateUtils.getStartTime(new Date(), -1);
        Long thirDay = DateUtils.getStartTime(new Date(), -2);

        String t_order_info = PojoTables.OrderInfo;
        String t_order_pay = PojoTables.OrderPay;
        if (null != SYS_SETTINGS.USE_BAK_ORDER && 0 == 1 - SYS_SETTINGS.USE_BAK_ORDER) {
            String dataDbName = OrderBakTaskUtils.getDataDbName();
            if (ValidateUtil.isNotNull(dataDbName)) {
                t_order_info = dataDbName + "." + PojoTables.D_ORDER_INFO;
                t_order_pay = dataDbName + "." + PojoTables.D_ORDER_PAY;
            }
        }

        if (null != SYS_SETTINGS.HAS_MULTILINGUAL && 0 == 1 - SYS_SETTINGS.HAS_MULTILINGUAL) {//多语言版
            if (ValidateUtil.isNotNull(city)) {
                city = JYPinyinHelper.isChinese(city) ? JYPinyinHelper.toHanyuPinyin(city) : city.toLowerCase();
            }
        }

        String sql = "SELECT COUNT(ori.orderno) orderCount, " +
                "       SUM(IFNULL(ori.totalPrice,0)) totalPrice, " +
                "       COUNT(DISTINCT ori.username) orderUserCount," +
                "       SUM(CASE WHEN ori.isRefundApply=2 THEN 1 ELSE 0 END) refundOrderCounts," +
                "       SUM(CASE WHEN ori.isRefundApply=2 THEN ori.totalPrice ELSE 0 END) refundOrderMoney, " +
                "       SUM(IFNULL(orp.platformMoney,0)) AS plateMoney, " +
                "       SUM(IFNULL(orp.shopMoney,0)) AS shopMoney, " +
                "       SUM(IFNULL(orp.postmanMoney,0)) AS postmanMoney, " +
                "       SUM(IFNULL(orp.cityAdminMoney,0)) AS cityAdminMoney," +
                "       SUM(IFNULL(orp.shopAwardMoney,0)) AS shopAwardMoney," +
                "       SUM(IFNULL(orp.platformAwardMoney,0)) AS platformAwardMoney," +
                "       FROM_UNIXTIME(ori.createTime/1000,'%Y-%m-%d') AS odate " +
                " FROM " + t_order_info + " AS ori " +
                " LEFT JOIN " + t_order_pay + " AS orp ON orp.orderno=ori.orderno " +
                " LEFT JOIN " + PojoTables.CITY_AGENT + " AS cag ON cag.id=ori.agentId " +
                " LEFT JOIN " + PojoTables.USER_AGENT_RELATION + " AS uar ON uar.agentId=cag.id " +
                " LEFT JOIN " + PojoTables.ShopInfo + " AS sfo on sfo.id=ori.shopId " +
                " WHERE ori.orderStatus=" + ORDER_STATUS.OVER + " AND ori.delFlag=0 " +
                (shopId != null && shopId > 0 ? " and ori.shopId=" + shopId : "") +
                (StringUtils.isNotEmpty(province) ? (" AND sfo.province='" + province + "'") : "") +
                (StringUtils.isNotEmpty(city) ? (" AND sfo.city='" + city + "'") : "") +
                (StringUtils.isNotEmpty(county) ? (" AND sfo.county='" + county + "'") : "") +
                " AND ori.createtime BETWEEN " + thirDay + " AND " + todayDay +
                (user.getUserType() == UserType.ADMIN ? (" AND uar.agentUsername='" + user.getUsername() + "'") : "") +
                " GROUP BY odate";
        String userAddSql = "SELECT COUNT(*) COUNTS,FROM_UNIXTIME(createtime,'%Y-%m-%d') udate FROM user_info " +
                " WHERE createtime between " + thirDay + " and " + todayDay +
                " GROUP BY udate ";
        List<HashMap<String, Object>> list = null;
        List<HashMap<String, Object>> userList = null;
        try {
            list = UserDaoSql.getInstance().queryBySql(sql);
            userList = UserDaoSql.getInstance().queryBySql(userAddSql);
        } catch (SQLException e) {
            return AjaxResult.errorResult(e.getMessage());
        }
        OrderFPVO vo = new OrderFPVO();
        if (list != null && !list.isEmpty()) {
            for (int i = 0; i < list.size(); i++) {
                String odate = list.get(i).get("odate").toString();
                Integer orderCount = Integer.parseInt(list.get(i).get("orderCount").toString());
                Integer orderUserCount = Integer.parseInt(list.get(i).get("orderUserCount").toString());
                Integer refundOrderCounts = Integer.parseInt(list.get(i).get("refundOrderCounts").toString());
                Double totalPrice = Double.parseDouble(list.get(i).get("totalPrice").toString());
                Double refundOrderMoney = Double.parseDouble(list.get(i).get("refundOrderMoney").toString());
                Double plateMoney = Double.parseDouble(list.get(i).get("plateMoney").toString());
                Double shopMoney = Double.parseDouble(list.get(i).get("shopMoney").toString());
                Double postmanMoney = Double.parseDouble(list.get(i).get("postmanMoney").toString());
                Double cityAdminMoney = Double.parseDouble(list.get(i).get("cityAdminMoney").toString());
                Double shopAwardMoney = Double.parseDouble(list.get(i).get("shopAwardMoney").toString());
                Double platformAwardMoney = Double.parseDouble(list.get(i).get("platformAwardMoney").toString());
                if (odate.equals(DateUtils.sdfDate.get().format(todayDay))) {       // 今日
                    vo.setFpMoney(totalPrice).setFpOrderCount(orderCount).setFpOrderUser(orderUserCount).setFpPostMoney(postmanMoney).setFpPlateMoney(plateMoney);
                    vo.setFpRefoundCount(refundOrderCounts).setFpRefoundMoney(refundOrderMoney);
                } else if (odate.equals(DateUtils.sdfDate.get().format(secondDay))) {   // 昨日
                    vo.setSeMoney(totalPrice).setSeOrderCount(orderCount).setSeOrderUser(orderUserCount).setSePostMoney(postmanMoney).setFpPlateMoney(plateMoney);
                    vo.setSeRefoundCount(refundOrderCounts).setSeRefoundMoney(refundOrderMoney);
                } else if (odate.equals(DateUtils.sdfDate.get().format(thirDay))) {     // 前天
                    vo.setTdMoney(totalPrice).setTdOrderCount(orderCount).setTdOrderUser(orderUserCount).setTdPostMoney(postmanMoney).setFpPlateMoney(plateMoney);
                    vo.setTdRefoundCount(refundOrderCounts).setTdRefoundMoney(refundOrderMoney);
                }
            }
        }
        if (userList != null && !userList.isEmpty()) {
            for (int i = 0; i < userList.size(); i++) {
                String udate = userList.get(i).get("udate").toString();
                Integer userCO = Integer.parseInt(userList.get(i).get("COUNTS").toString());
                if (udate.equals(DateUtils.sdfDate.get().format(todayDay))) {       // 今日
                    vo.setFpUserAdd(userCO);
                } else if (udate.equals(DateUtils.sdfDate.get().format(secondDay))) {   // 昨日
                    vo.setSeUserAdd(userCO);
                } else if (udate.equals(DateUtils.sdfDate.get().format(thirDay))) {     // 前天
                    vo.setTdUserAdd(userCO);
                }
            }
        }

        return AjaxResult.successResult(vo);
    }


    // 靠谱送 - 用户跑腿订单统计
    @RequestMapping(method = RequestMethod.POST, value = "/user/order/paotui/static/list")
    @ResponseBody
    public String user_order_paotui_static(String token, Integer orderType, String orderNo, Long startTime, Long endTime, Integer isExport, Integer page, Integer size) {
        UserInfo user = UserInfoMemory.getInstance().getUserInfo(UserOnlineImpl.getInstance().get(token));
        if (user == null) {
            return AjaxResult.errorLogoutResult();
        }
        /**
         * SELECT
         *   OT.times AS times,
         *   COUNT(OT.orderNo) AS totalOrderCount,
         *   SUM(OT.totalWeight) AS totalWeight,
         *   SUM(OT.distance) AS totalDistance,
         *   SUM(OT.goodsPrice) AS totalGoodsPrice,
         *   SUM(
         *     CASE
         *       WHEN OT.orderStatus IN (3, 1)
         *       OR OT.isRefundApply = 2
         *       THEN 1
         *       ELSE 0
         *     END
         *   ) AS cancelOrderCount,
         *   SUM(
         *     CASE
         *       WHEN OT.orderStatus NOT IN (3, 1)
         *       AND OT.isRefundApply != 2
         *       THEN 1
         *       ELSE 0
         *     END
         *   ) AS vaildOrderCount
         * FROM
         *   (SELECT
         *     FROM_UNIXTIME(OI.createTime / 1000, '%Y-%m-%d') AS times,
         *     OI.orderNo,
         *     IFNULL(oth.totalWeight, 0.0) totalWeight,
         *     IFNULL(OI.distance, 0.0) distance,
         *     IFNULL(oth.goodsPrice, 0.0) goodsPrice,
         *     OI.username,
         *     OI.orderStatus,
         *     OI.isRefundApply
         *   FROM
         *     other_order_info AS oth
         *     LEFT JOIN order_info AS OI
         *       ON oth.orderNo = OI.orderNo
         *       AND OI.delFlag = 0
         *   WHERE OI.username = '17855555555'
         *   AND OI.orderType= 2
         *     AND OI.delFlag = 0) OT
         * GROUP BY OT.times
         * ORDER BY OT.times DESC ;
         */
        if (page == null) {
            page = 1;
        }
        if (size == null) {
            size = 10;
        }

        String sql = " SELECT   OT.times AS times,COUNT(OT.orderNo) AS totalOrderCount,  SUM(OT.totalWeight) AS totalWeight,  SUM(OT.distance) AS totalDistance, SUM(OT.goodsPrice) AS totalGoodsPrice, "
                + "  SUM(CASE WHEN OT.orderStatus IN (" + StringUtils.join(ORDER_STATUS.userOrderCancelSet, ",") + ") OR OT.isRefundApply = 2 THEN 1 ELSE 0 END) AS cancelOrderCount, "
                + " SUM(CASE WHEN OT.orderStatus NOT IN (3, 1) AND OT.isRefundApply != 2 THEN 1 ELSE 0 END) AS validOrderCount "
                + " FROM (SELECT "
                + "      FROM_UNIXTIME(OI.createTime / 1000, '%Y-%m-%d') AS times, "
                + "      OI.orderNo, "
                + "      IFNULL(oth.totalWeight, 0.0) totalWeight, "
                + "      IFNULL(OI.distance, 0.0) distance, "
                + "      IFNULL(oth.goodsPrice, 0.0) goodsPrice, "
                + "      OI.username, "
                + "      OI.orderStatus,"
                + "      OI.isRefundApply "
                + " FROM    " + PojoTables.OTHER_ORDER_INFO + " AS oth "
                + " LEFT JOIN " + PojoTables.OrderInfo + " AS OI  "
                + "   ON oth.orderNo = OI.orderNo   AND OI.delFlag = 0  "
                + "  WHERE OI.username = '" + user.getUsername() + "'";
        if (orderType != null) {
            sql += " AND OI.orderType= 2 ";
        }
        if (ValidateUtil.isNotNull(orderNo)) {
            sql += " AND OI.orderNo like '%" + orderNo + "%'";
        }
        if (ValidateUtil.isAbsLong(startTime) && ValidateUtil.isAbsLong(endTime)) {
            sql += " AND OI.createTime >" + startTime + " and OI.createTime <" + endTime;
        }
        sql += " AND OI.delFlag = 0) OT " +
                "GROUP BY OT.times " +
                "ORDER BY OT.times DESC ";

        List<HashMap<String, Object>> hashMaps = null;

        try {
            hashMaps = UserDaoSql.getInstance().queryBySql(sql.toString());
        } catch (Exception e) {
            e.printStackTrace();
        }
        JSONObject jsonObject = new JSONObject();
        jsonObject.put("status", 1);
        if (null != isExport && isExport - 1 == 0) {
            String downloadUrl = exportExcel(hashMaps);
            if (ValidateUtil.isNotNull(downloadUrl)) {
                downloadUrl = SYS_CONFIG_PATH.IMAGE_BASE_URL + downloadUrl;
            }
            return AjaxResult.successResult(downloadUrl);
        } else {
            if (ValidateUtil.isAbsList(hashMaps)) {
                Map<String, Object> info = compute(hashMaps);
                jsonObject.put("info", info);
                JYPageUtils.PageList pageList = JYPageUtils.getList(hashMaps, page, size);
                if (pageList != null && ValidateUtil.isAbsList(pageList.list)) {
                    jsonObject.put("totalCount", pageList.totalCount);
                    jsonObject.put("data", pageList.list);
                    return jsonObject.toString();
                }
                jsonObject.put("data", null);
                jsonObject.put("totalCount", hashMaps.size());
                return jsonObject.toString();
            }
            jsonObject.put("size", 0);
            jsonObject.put("totalCount", 0);
            jsonObject.put("info", null);
            jsonObject.put("data", null);
            return jsonObject.toString();
        }
    }

    // 计算所有的数据
    private Map<String, Object> compute(List<HashMap<String, Object>> hashMaps) {
        Long totalOrderCount = 0L;
        Double totalDistance = 0.0d;
        Double totalWeight = 0.0d;
        Double totalGoodsPrice = 0.0d;
        Long totalCancelOrderCount = 0L;
        for (Map<String, Object> temp : hashMaps) {
            totalOrderCount += Long.parseLong(temp.get("totalOrderCount").toString());
            totalDistance = JYMathDoubleUtils.add(totalDistance, Double.parseDouble(temp.get("totalDistance").toString()));
            totalWeight = JYMathDoubleUtils.add(totalWeight, Double.parseDouble(temp.get("totalWeight").toString()));
            totalGoodsPrice = JYMathDoubleUtils.add(totalGoodsPrice, Double.parseDouble(temp.get("totalGoodsPrice").toString()));
            totalCancelOrderCount += Long.parseLong(temp.get("cancelOrderCount").toString());
        }
        Map<String, Object> map = new HashMap<>();
        map.put("totalOrderCount", totalOrderCount);
        map.put("totalDistance", totalDistance);
        map.put("totalWeight", totalWeight);
        map.put("totalGoodsPrice", totalGoodsPrice);
        map.put("cancelOrderCount", totalCancelOrderCount);
        return map;
    }

    // ===================
    // 导excel
    private String exportExcel(List<HashMap<String, Object>> list) {
        if (!ValidateUtil.isAbsList(list)) {
            return null;
        }
        // Integer hasWallet = SysSettingMobileMemory.getInstance().getValueByCode(SysSettingKey.HAS_WALLET);
        String outputPath = "";
        try {
            outputPath = SYS_CONFIG_PATH.ORDER_IMAGE + "/" + DateUtils.getCurrentYearMonth() + "/" + DateUtils.getCurrTime() + ".xls";
            File outputFile = new File(SYS_CONFIG_PATH.IMAGE_BASE_FILE + outputPath);
            FileUtils.forceMkdirParent(outputFile);
            WritableWorkbook workbook = Workbook.createWorkbook(outputFile);
            WritableSheet sheet = workbook.createSheet("sheet1", 0);

            jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#0.00");   // 设置数字格式
            jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf); // 设置表单格式

            int col = 0;   // 列
            int row = 0;  // 行
            sheet.addCell(new Label(col++, row, "日期"));
            sheet.addCell(new Label(col++, row, "有效单量"));

            sheet.addCell(new Label(col++, row, "物品重量"));
            // if (hasWallet == 1) {
                sheet.addCell(new Label(col++, row, "距离"));
                sheet.addCell(new Label(col++, row, "物品总价"));
            // }
            sheet.addCell(new Label(col++, row, "取消订单量"));

            row = 1;
            if (ValidateUtil.isAbsList(list)) {
                for (HashMap<String, Object> map : list) {
                    col = 0;
                    sheet.addCell(new Label(col++, row, map.get("times") + ""));
                    sheet.addCell(new Label(col++, row, map.get("validOrderCount") + ""));
                    sheet.addCell(new Label(col++, row, map.get("totalWeight") + ""));
                    // if (hasWallet == 1) {
                        sheet.addCell(new Label(col++, row, map.get("totalDistance") + ""));
                        sheet.addCell(new Label(col++, row, map.get("totalGoodsPrice") + ""));
                    // }
                    sheet.addCell(new Label(col++, row, map.get("cancelOrderCount") + ""));
                    row++;     // 行
                }
            }
            workbook.write();
            workbook.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return outputPath;
    }


    // 靠谱送 - 管理后台 - 跑腿订单统计
    @RequestMapping(method = RequestMethod.POST, value = "/admin/paotui/order/statistics/by-shop/list")
    @ResponseBody
    public String admin_order_paotui_by_shop_list(String token, String city, String county, Long agentId, Long shopId, Long startTime, Long endTime, Integer isExport, Integer page, Integer size) {
        UserInfo user = UserInfoMemory.getInstance().getUserInfo(UserOnlineImpl.getInstance().get(token));
        if (user == null) {
            return AjaxResult.errorLogoutResult();
        }
        if (!User.hasAdminAuth(user.getUserType())) {
            return AjaxResult.errorResult("没有操作权限");
        }
        if (page == null) {
            page = 1;
        }
        if (size == null) {
            size = 15;
        }
        Set<Long> agentIdSet = new HashSet<>();
        if (user.getUserType() == UserType.ADMIN || user.getUserType() == UserType.COMPANY) {
            city = null;
            county = null;
            String sysCityAdminUsername = user.getUsername();

            if ((agentIdSet == null || agentIdSet.isEmpty()) && shopId == null) {
                return AjaxResult.successResult(null, 0);
            }
        } else {
            if (agentId != null) {
                agentIdSet.add(agentId);
            }
        }

        /**
         * SELECT
         * SI.shopName,
         * SI.id AS shopId,
         *  SI.ownUsername,
         *  IFNULL(T.totalOrderCount,0) totalOrderCount,
         *  IFNULL(T.validOrderCount,0) validOrderCount,
         *  IFNULL(T.validGoodsPrice,0) validGoodsPrice,
         * IFNULL(T.validTotalDistance,0) validTotalDistance,
         * IFNULL(T.cancelOrderCount,0) cancelOrderCount,
         * IFNULL(T.cancelGoodsPrice,0) cancelGoodsPrice,
         * IFNULL(T.cancelTotalWeight,0) cancelTotalWeight,
         * IFNULL(T.cancelTotalDistance,0) cancelTotalDistance,
         * IFNULL(T.postCancelOrderCount,0) postCancelOrderCount,
         * IFNULL(T.postCancelGoodsPrice,0) postCancelGoodsPrice,
         * IFNULL(T.postCancelTotalWeight,0) postCancelTotalWeight,
         * IFNULL(T.postCancelTotalDistance,0)  postCancelTotalDistance
         *
         *   FROM shop_info AS SI
         * LEFT JOIN (
         * 	SELECT
         *   OI.username,
         *   COUNT(OI.orderNo) AS totalOrderCount,
         *   SUM(CASE WHEN OI.orderStatus =9 AND OI.isRefundApply !=2 THEN 1 ELSE 0 END) AS validOrderCount,
         *   SUM(CASE WHEN OI.orderStatus =9 AND OI.isRefundApply !=2 THEN  oth.goodsPrice ELSE 0 END) AS validGoodsPrice,
         *   SUM(CASE WHEN OI.orderStatus =9 AND OI.isRefundApply !=2 THEN  oth.totalWeight ELSE 0 END) AS validTotalWeight,
         *   SUM(CASE WHEN OI.orderStatus =9 AND OI.isRefundApply !=2 THEN  OI.distance ELSE 0 END) AS validTotalDistance,
         *   SUM(CASE WHEN OI.orderStatus!= 9 AND oth.isPostingCancel = 0 THEN 1 ELSE 0 END) AS cancelOrderCount,
         *   SUM(CASE WHEN OI.orderStatus !=9 AND oth.isPostingCancel =0 THEN  oth.goodsPrice ELSE 0 END) AS cancelGoodsPrice,
         *   SUM(CASE WHEN OI.orderStatus !=9 AND oth.isPostingCancel =0 THEN  oth.totalWeight ELSE 0 END) AS cancelTotalWeight,
         *   SUM(CASE WHEN OI.orderStatus !=9 AND oth.isPostingCancel =0 THEN  OI.distance ELSE 0 END) AS cancelTotalDistance,
         *   SUM(CASE WHEN OI.orderStatus!= 9 AND oth.isPostingCancel = 1 THEN 1 ELSE 0 END) AS postCancelOrderCount,
         *   SUM(CASE WHEN OI.orderStatus !=9 AND oth.isPostingCancel =1 THEN  oth.goodsPrice ELSE 0 END) AS postCancelGoodsPrice,
         *   SUM(CASE WHEN OI.orderStatus !=9 AND oth.isPostingCancel =1 THEN  oth.totalWeight ELSE 0 END) AS postCancelTotalWeight,
         *   SUM(CASE WHEN OI.orderStatus !=9 AND oth.isPostingCancel =1 THEN  OI.distance ELSE 0 END) AS  postCancelTotalDistance
         * FROM
         *   other_order_info oth
         *   INNER JOIN order_info OI
         *     ON OI.orderNo = oth.orderNo
         *     AND OI.delFlag = 0  WHERE  OI.orderStatus IN (9,3,41,4) AND oth.delFlag = 0
         * GROUP BY OI.username
         * ) AS T ON T.username = SI.ownUsername WHERE SI.delFlag = 0;
         */
        // count -------
        StringBuilder countSql = new StringBuilder();
        countSql.append(" select count(DISTINCT(SI.id)) as counts from ").append(PojoTables.ShopInfo)
                .append(" SI  ");
        if (shopId == null && (agentIdSet != null && !agentIdSet.isEmpty())) {
            countSql.append(" LEFT JOIN ").append(PojoTables.SHOP_AGENT_RELATION).append(" SR ")
                    .append(" on SR.delFlag = 0 and SR.shopId = SI.Id ");
        }
        countSql.append(" where SI.delFlag = 0  ");
        if (shopId != null) {
            countSql.append("  and  SI.id =  ").append(shopId);
        } else if (agentIdSet != null && !agentIdSet.isEmpty()) {
            countSql.append(" and SR.agentId in (").append(StringUtils.join(agentIdSet, ",")).append(") ");
        } else if (ValidateUtil.isNotNull(city)) {
            countSql.append(" and SI.city = '").append(city).append("' ");
            if (ValidateUtil.isNotNull(county)) {
                countSql.append(" and SI.county = '").append(county).append("' ");
            }
        }
        Set<Integer> orderStatusSet = new HashSet<>();
        orderStatusSet.add(ORDER_STATUS.OVER);
        orderStatusSet.add(ORDER_STATUS.CANCEL_BY_SYSTEM);
        orderStatusSet.add(ORDER_STATUS.PAYED_CANCEL);
        orderStatusSet.add(ORDER_STATUS.CANCEL);
        // ----- sql
        StringBuilder selectSql = new StringBuilder();
        selectSql.append(" select ")
                .append("  SI.shopName, ")
                .append("  SI.id AS shopId, ")
                .append("  SI.ownUsername, ")
                .append("  IFNULL(T.totalOrderCount,0) totalOrderCount,")
                .append("  IFNULL(T.validOrderCount,0) validOrderCount,")
                .append("  IFNULL(T.validGoodsPrice,0) validGoodsPrice,")
                .append("  IFNULL(T.validTotalWeight,0) validTotalWeight,")
                .append("  IFNULL(T.validTotalDistance,0) validTotalDistance,")
                .append("  IFNULL(T.cancelOrderCount,0) cancelOrderCount,")
                .append("  IFNULL(T.cancelGoodsPrice,0) cancelGoodsPrice,")
                .append("  IFNULL(T.cancelTotalWeight,0) cancelTotalWeight,")
                .append("  IFNULL(T.cancelTotalDistance,0) cancelTotalDistance,")
                .append("  IFNULL(T.postCancelOrderCount,0) postCancelOrderCount,")
                .append("  IFNULL(T.postCancelGoodsPrice,0) postCancelGoodsPrice,")
                .append("  IFNULL(T.postCancelTotalWeight,0) postCancelTotalWeight,")
                .append("  IFNULL(T.postCancelTotalDistance,0)  postCancelTotalDistance ")
                .append("   FROM shop_info AS SI ")
                .append("      LEFT JOIN ( ")
                .append("            SELECT ")
                .append("                 OI.username,")
                .append("                 COUNT(OI.orderNo) AS totalOrderCount,")
                .append("                 SUM(CASE WHEN OI.orderStatus =").append(ORDER_STATUS.OVER).append(" AND OI.isRefundApply !=").append(REFUND_APPLY_STATUS.PASS).append(" THEN 1 ELSE 0 END) AS validOrderCount,")
                .append("                 SUM(CASE WHEN OI.orderStatus =").append(ORDER_STATUS.OVER).append(" AND OI.isRefundApply !=").append(REFUND_APPLY_STATUS.PASS).append(" THEN  oth.goodsPrice ELSE 0 END) AS validGoodsPrice,")
                .append("                 SUM(CASE WHEN OI.orderStatus =").append(ORDER_STATUS.OVER).append(" AND OI.isRefundApply !=").append(REFUND_APPLY_STATUS.PASS).append(" THEN  oth.totalWeight ELSE 0 END) AS validTotalWeight,")
                .append("                 SUM(CASE WHEN OI.orderStatus =").append(ORDER_STATUS.OVER).append(" AND OI.isRefundApply !=").append(REFUND_APPLY_STATUS.PASS).append(" THEN  OI.distance ELSE 0 END) AS validTotalDistance,")
                .append("                 SUM(CASE WHEN OI.orderStatus!= ").append(ORDER_STATUS.OVER).append(" AND oth.isPostingCancel = 0 THEN 1 ELSE 0 END) AS cancelOrderCount,")
                .append("                 SUM(CASE WHEN OI.orderStatus !=").append(ORDER_STATUS.OVER).append(" AND oth.isPostingCancel =0 THEN  oth.goodsPrice ELSE 0 END) AS cancelGoodsPrice,")
                .append("                 SUM(CASE WHEN OI.orderStatus !=").append(ORDER_STATUS.OVER).append(" AND oth.isPostingCancel =0 THEN  oth.totalWeight ELSE 0 END) AS cancelTotalWeight,")
                .append("                 SUM(CASE WHEN OI.orderStatus !=").append(ORDER_STATUS.OVER).append(" AND oth.isPostingCancel =0 THEN  OI.distance ELSE 0 END) AS cancelTotalDistance,")
                .append("                 SUM(CASE WHEN OI.orderStatus!= ").append(ORDER_STATUS.OVER).append(" AND oth.isPostingCancel = 1 THEN 1 ELSE 0 END) AS postCancelOrderCount,")
                .append("                 SUM(CASE WHEN OI.orderStatus !=").append(ORDER_STATUS.OVER).append(" AND oth.isPostingCancel =1 THEN  oth.goodsPrice ELSE 0 END) AS postCancelGoodsPrice,")
                .append("                 SUM(CASE WHEN OI.orderStatus !=").append(ORDER_STATUS.OVER).append(" AND oth.isPostingCancel =1 THEN  oth.totalWeight ELSE 0 END) AS postCancelTotalWeight,")
                .append("                 SUM(CASE WHEN OI.orderStatus !=").append(ORDER_STATUS.OVER).append(" AND oth.isPostingCancel =1 THEN  OI.distance ELSE 0 END) AS  postCancelTotalDistance ")
                .append("           FROM ")
                .append("               other_order_info oth ")
                .append("                INNER JOIN order_info OI ")
                .append("                     ON OI.orderNo = oth.orderNo ")
                .append("                      AND OI.delFlag = 0  WHERE  OI.orderStatus IN (").append(StringUtils.join(orderStatusSet, ",")).append(") AND oth.delFlag = 0 ");
        ShopInfo shop = ShopMemory.getInstance().getShopById(shopId);
        if (shopId != null && shop != null) {
            selectSql.append(" AND OI.username = '").append(shop.getOwnUsername()).append("' ");
        } else if (agentIdSet != null && !agentIdSet.isEmpty()) {
            if (agentIdSet.size() == 1) {
                selectSql.append(" and OI.agentId  = ").append(agentIdSet.iterator().next());
            } else {
                selectSql.append(" and OI.agentId  in (").append(StringUtils.join(agentIdSet, ",")).append(") ");
            }
        }
        if (ValidateUtil.isAbsLong(startTime)) {
            selectSql.append(" and OI.createTime > ").append(startTime);
        }
        if (ValidateUtil.isAbsLong(endTime)) {
            selectSql.append(" and OI.createTime < ").append(endTime);
        }
        selectSql.append("                     GROUP BY OI.username ")
                .append(" ) AS T ON T.username = SI.ownUsername where SI.delFlag = 0 ");
        if (shopId != null) {
            selectSql.append(" and SI.id = ").append(shopId);
        } else if (agentIdSet != null && !agentIdSet.isEmpty()) {
            selectSql.append(" and SI.id in (")
                    .append(" select DISTINCT(shopId) from ").append(PojoTables.SHOP_AGENT_RELATION)
                    .append(" where delFlag = 0 and agentId in (").append(StringUtils.join(agentIdSet, ",")).append(") ")
                    .append(")");
        } else if (ValidateUtil.isNotNull(city)) {
            selectSql.append(" and SI.city = '").append(city).append("' ");
            if (ValidateUtil.isNotNull(county)) {
                selectSql.append(" and SI.county = '").append(county).append("' ");
            }
        }
        selectSql.append(" order by SI.id ");
        if (isExport == null || isExport != 1) {
            selectSql.append(" limit ").append((page - 1) * size).append(",").append(size);
        }
        Long count = 0l;
        List<HashMap<String, Object>> list = null;
        try {
            if (isExport == null || isExport != 1) {
                count = UserDaoSql.getInstance().getCount(countSql.toString());
            }
            list = UserDaoSql.getInstance().queryBySql(selectSql.toString());
        } catch (Exception e) {
            e.printStackTrace();
            return AjaxResult.errorResult(e.getMessage());
        }
        // 下载excel
        if (isExport != null && isExport == 1) {
            String downloadUrl = generatePoatuiStatisExcel(list);
            if (ValidateUtil.isNotNull(downloadUrl) && ValidateUtil.isNotNetUrl(downloadUrl)) {
                downloadUrl = SYS_CONFIG_PATH.IMAGE_BASE_URL + downloadUrl;
            }
            return AjaxResult.successResult(downloadUrl);
        }
        return AjaxResult.successResult(list, count);
    }


    // 获取顶


    /**
     * 生成跑腿商家订单统计
     *
     * @param list
     * @return
     */
    private String generatePoatuiStatisExcel(List<HashMap<String, Object>> list) {
        if (!ValidateUtil.isAbsList(list)) {
            return null;
        }
        String outputPath = "";
        try {
            outputPath = SYS_CONFIG_PATH.ORDER_IMAGE + "/" + DateUtils.getCurrentYearMonth() + "/" + DateUtils.getCurrTime() + ".xls";
            File outputFile = new File(SYS_CONFIG_PATH.IMAGE_BASE_FILE + outputPath);
            FileUtils.forceMkdirParent(outputFile);
            WritableWorkbook workbook = Workbook.createWorkbook(outputFile);
            WritableSheet sheet = workbook.createSheet("sheet1", 0);

            jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#0.00");   // 设置数字格式
            jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf); // 设置表单格式

            int col = 0;   // 列
            int row = 0;  // 行
            sheet.addCell(new Label(col++, row, "序列"));
            sheet.addCell(new Label(col++, row, "店铺名"));
            sheet.addCell(new Label(col++, row, "店铺账号"));

            sheet.addCell(new Label(col++, row, "完成订单量"));
            sheet.addCell(new Label(col++, row, "完成订单商品价格"));
            sheet.addCell(new Label(col++, row, "完成订单商品重量"));
            sheet.addCell(new Label(col++, row, "完成订单商品距离"));

            sheet.addCell(new Label(col++, row, "待取货取消订单数量"));
            sheet.addCell(new Label(col++, row, "待取货取消订单商品价格"));
            sheet.addCell(new Label(col++, row, "待取货取消订单商品重量"));
            sheet.addCell(new Label(col++, row, "待取货取消订单总距离"));

            sheet.addCell(new Label(col++, row, "配送中取消订单数量"));
            sheet.addCell(new Label(col++, row, "配送中取消订单商品价格"));
            sheet.addCell(new Label(col++, row, "配送中取消订单商品重量"));
            sheet.addCell(new Label(col++, row, "配送中取消订单总距离"));

            row = 1;
            if (ValidateUtil.isAbsList(list)) {
                for (HashMap<String, Object> map : list) {
                    col = 0;
                    sheet.addCell(new Label(col++, row, map.get("shopId") + ""));
                    sheet.addCell(new Label(col++, row, map.get("shopName") + ""));
                    sheet.addCell(new Label(col++, row, map.get("ownUsername") + ""));
                    sheet.addCell(new Label(col++, row, map.get("validOrderCount") + ""));
                    sheet.addCell(new Label(col++, row, map.get("validGoodsPrice") + ""));
                    sheet.addCell(new Label(col++, row, map.get("validTotalWeight") + ""));
                    sheet.addCell(new Label(col++, row, map.get("validTotalDistance") + ""));
                    sheet.addCell(new Label(col++, row, map.get("cancelOrderCount") + ""));
                    sheet.addCell(new Label(col++, row, map.get("cancelGoodsPrice") + ""));
                    sheet.addCell(new Label(col++, row, map.get("cancelTotalWeight") + ""));
                    sheet.addCell(new Label(col++, row, map.get("cancelTotalDistance") + ""));
                    sheet.addCell(new Label(col++, row, map.get("postCancelOrderCount") + ""));
                    sheet.addCell(new Label(col++, row, map.get("postCancelGoodsPrice") + ""));
                    sheet.addCell(new Label(col++, row, map.get("postCancelTotalWeight") + ""));
                    sheet.addCell(new Label(col++, row, map.get("postCancelTotalDistance") + ""));
                    row++;     // 行
                }
            }
            workbook.write();
            workbook.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return outputPath;
    }


    // 靠谱送-管理后台 - 商家跑腿统计  显示订单的详情信息
    @RequestMapping(method = RequestMethod.POST, value = "/admin/paotui/order/statistics/detail/order/list")
    @ResponseBody
    public String shop_paotui_order_list(String token, Long shopId, Long startTime, Long endTime, Integer state, Integer isExport, Integer isPostIngCancel, Integer page, Integer size) {
        UserInfo user = UserInfoMemory.getInstance().getUserInfo(UserOnlineImpl.getInstance().get(token));
        if (user == null) {
            return AjaxResult.errorLogoutResult();
        }
        if (!User.hasAdminAuth(user.getUserType())) {
            return AjaxResult.errorResult("没有操作权限");
        }
        if (page == null) {
            page = 1;
        }
        if (size == null) {
            size = 15;
        }
        Set<Integer> orderStatusSet = new HashSet<>();
        orderStatusSet.add(ORDER_STATUS.OVER);
        orderStatusSet.add(ORDER_STATUS.CANCEL_BY_SYSTEM);
        orderStatusSet.add(ORDER_STATUS.PAYED_CANCEL);
        orderStatusSet.add(ORDER_STATUS.CANCEL);
        StringBuilder sql = new StringBuilder();
        sql.append(" from ").append(PojoTables.OTHER_ORDER_INFO).append("  oth ")
                .append(" inner join ").append(PojoTables.OrderInfo).append(" OI on OI.orderNo = oth.OrderNo and OI.delFlag = 0 where oth.delFlag = 0 ");
        sql.append(" and OI.orderStatus in  (").append(StringUtils.join(orderStatusSet, ",")).append(") ");
        ShopInfo shop = ShopMemory.getInstance().getShopById(shopId);
        if (shopId == null || shop == null) {
            return AjaxResult.successResult(null, 0);
        }
        sql.append(" and OI.username ='").append(shop.getOwnUsername()).append("' ");

        if (ValidateUtil.isAbsLong(startTime)) {
            sql.append(" and OI.createTime > ").append(startTime);
        }
        if (ValidateUtil.isAbsLong(endTime)) {
            sql.append(" and OI.createTime < ").append(endTime);
        }
        if (state != null && state == 1) {
            sql.append(" and OI.orderStatus = ").append(ORDER_STATUS.OVER).append(" and OI.isRefundApply !=").append(REFUND_APPLY_STATUS.PASS);
        } else if (state != null && state == 2) {
            sql.append(" and OI.orderStatus != ").append(ORDER_STATUS.OVER);
            if (isPostIngCancel != null) {
                sql.append(" and oth.isPostingCancel = ").append(isPostIngCancel);
            }
        }
        String countSql = "select count(1) as counts " + sql.toString();
        String selectSQL = " select " +
                " OI.orderNo, " +
                " OI.shopName, " +
                " OI.username, " +
                " IFnull(OI.distance,0) as distance, " +
                " IFnull(Oth.goodsPrice,0) as goodsPrice, " +
                " IFnull(Oth.totalWeight,0) as totalWeight, " +
                " OI.createTime, " +
                " OI.orderStatus, " +
                " OI.address, " +
                " OI.buyer, " +
                " OI.telephone "
                + sql.toString() + " order by OI.createTime desc  ";
        if (isExport == null || isExport != 1) {
            selectSQL += " limit " + (page - 1) * size + "," + size;
        }
        Long count = 0L;
        List<HashMap<String, Object>> list = null;
        try {
            if (isExport == null || isExport != 1) {
                count = UserDaoSql.getInstance().getCount(countSql);
            }
            list = UserDaoSql.getInstance().queryBySql(selectSQL);
        } catch (Exception e) {
            e.printStackTrace();
            return AjaxResult.errorResult(e.getMessage());
        }
        if (isExport != null && isExport == 1) {
            String downloadUrl = generatePoatuiStatisDetailExcel(list);
            if (ValidateUtil.isNotNull(downloadUrl) && ValidateUtil.isNotNetUrl(downloadUrl)) {
                downloadUrl = SYS_CONFIG_PATH.IMAGE_BASE_URL + downloadUrl;
            }
            return AjaxResult.successResult(downloadUrl);
        }
        return AjaxResult.successResult(list, count);
    }

    /**
     * 生成跑腿商家订单统计
     *
     * @param list
     * @return
     */
    private String generatePoatuiStatisDetailExcel(List<HashMap<String, Object>> list) {
        if (!ValidateUtil.isAbsList(list)) {
            return null;
        }
        String outputPath = "";
        try {
            outputPath = SYS_CONFIG_PATH.ORDER_IMAGE + "/" + DateUtils.getCurrentYearMonth() + "/" + DateUtils.getCurrTime() + ".xls";
            File outputFile = new File(SYS_CONFIG_PATH.IMAGE_BASE_FILE + outputPath);
            FileUtils.forceMkdirParent(outputFile);
            WritableWorkbook workbook = Workbook.createWorkbook(outputFile);
            WritableSheet sheet = workbook.createSheet("sheet1", 0);

            jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#0.00");   // 设置数字格式
            jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf); // 设置表单格式

            int col = 0;   // 列
            int row = 0;  // 行
            sheet.addCell(new Label(col++, row, "时间"));
            sheet.addCell(new Label(col++, row, "订单号"));
            sheet.addCell(new Label(col++, row, "店铺名称"));
            sheet.addCell(new Label(col++, row, "下单账号"));

            sheet.addCell(new Label(col++, row, "商品金额"));
            sheet.addCell(new Label(col++, row, "商品重量"));
            sheet.addCell(new Label(col++, row, "距离"));
            sheet.addCell(new Label(col++, row, "姓名"));

            sheet.addCell(new Label(col++, row, "地址"));
            sheet.addCell(new Label(col++, row, "电话"));
            row = 1;
            if (ValidateUtil.isAbsList(list)) {
                for (HashMap<String, Object> map : list) {
                    col = 0;
                    sheet.addCell(new Label(col++, row, DateUtils.sdfDateTime.get().format(new Date(Long.parseLong(map.get("createTime").toString())))));
                    sheet.addCell(new Label(col++, row, map.get("orderNo") + ""));
                    sheet.addCell(new Label(col++, row, map.get("shopName") + ""));
                    sheet.addCell(new Label(col++, row, map.get("username") + ""));
                    sheet.addCell(new Label(col++, row, map.get("goodsPrice") + ""));
                    sheet.addCell(new Label(col++, row, map.get("totalWeight") + ""));
                    sheet.addCell(new Label(col++, row, map.get("distance") + ""));
                    sheet.addCell(new Label(col++, row, map.get("buyer") + ""));
                    sheet.addCell(new Label(col++, row, map.get("address") + ""));
                    sheet.addCell(new Label(col++, row, map.get("telephone") + ""));
                    row++;     // 行
                }
            }
            workbook.write();
            workbook.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return outputPath;
    }


}
